﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BI.CF.IDAL;
using BI.CF.Model;
using System.Data.SqlClient;
using System.Data;
using BI.CF.PageList;
using System.Configuration;

namespace BI.CF.DAL
{
    public class MemLoanDAL : MemLoanIDAL
    {
        public static string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        /// <summary>
        /// 显示信用标
        /// </summary>
        /// <param name="pageindex">当前第几页页</param>
        /// <param name="pagesize">当前页显示几条数据</param>
        /// <returns></returns>
        public SqlDataReader Show_MemlLoan(int pageindex, int pagesize, string UserName, decimal money, string fuhao)
        {
            SqlParameter[] paremeter ={ new  SqlParameter("@PageSize",SqlDbType.Int),
                                        new SqlParameter("@PageIndex",SqlDbType.Int),
                                        new SqlParameter("@UserNames",SqlDbType.VarChar),
                                        new SqlParameter("@LoanMoney",SqlDbType.Decimal),
                                         new SqlParameter("@BiJiaoFuHao",SqlDbType.VarChar)
                                 };
            paremeter[0].Value = pagesize;
            paremeter[1].Value = pageindex;
            paremeter[2].Value = UserName;
            paremeter[3].Value = money;
            paremeter[4].Value = fuhao;
            SqlDataReader read = DbHelperSQL.RunProcedure("PROC_MemLoan_Show", paremeter);
            #region 注释掉的
            //Pager<MemLoanModel> list = new Pager<MemLoanModel>();
            //List<MemLoanModel> lists = new List<MemLoanModel>();
            //while (read.Read())
            //{
            //    MemLoanModel m = new MemLoanModel();
            //    m.MemLoanId = int.Parse(read["MemLoanId"].ToString());
            //    m.LoanMoney = decimal.Parse(read["LoanMoney"].ToString());
            //    m.LoanPurpose = read["LoanPurpose"].ToString();
            //    m.LoanRaise = DateTime.Parse(read["LoanRaise"].ToString());
            //    m.Annualrate = float.Parse(read["Annualrate"].ToString());
            //    m.LoanTerm = int.Parse(read["LoanTerm"].ToString());
            //    m.RepaymentModeId = read["RepaymentModeId"].ToString();
            //    m.Title = read["Title"].ToString();
            //    m.LoanExplain = read["LoanExplain"].ToString();
            //    m.UserrId = int.Parse(read["UserrId"].ToString());
            //    m.LaunchLoanTime = DateTime.Parse(read["LaunchLoanTime"].ToString());
            //    m.EndLoanTime = DateTime.Parse(read["EndLoanTime"].ToString());
            //    m.RepaymentDay = DateTime.Parse(read["RepaymentDay"].ToString());
            //    m.PayInterest = decimal.Parse(read["PayInterest"].ToString());
            //    m.RepaySumMoney = decimal.Parse(read["RepaySumMoney"].ToString());
            //    m.ExamineState = int.Parse(read["ExamineState"].ToString());
            //    m.AlreadyRaiseLoan = decimal.Parse(read["AlreadyRaiseLoan"].ToString());
            //    m.TotalTransactions = int.Parse(read["TotalTransactions"].ToString());
            //    m.LoanType = read["LoanType"].ToString();
            //    lists.Add(m);
            //};
            //list.Data = lists; 
            #endregion
            return read;
        }

        /// <summary>
        /// 返回信用标的显示
        /// </summary>
        /// <param name="UserName">用户名</param>
        /// <param name="LoanMoney">金额</param>
        /// <param name="BiJiaoFuHao">比较符号</param>
        /// <returns></returns>
        public int Count_MemLoan(string UserName, decimal LoanMoney, string BiJiaoFuHao)
        {
            #region
            //SqlParameter[] paremeter ={
            //                            new SqlParameter("@UserNames",SqlDbType.VarChar),
            //                            new SqlParameter("@LoanMoney",SqlDbType.Decimal),
            //                             new SqlParameter("@BiJiaoFuHao",SqlDbType.VarChar)
            //                     };
            //paremeter[0].Value = UserName;
            //paremeter[1].Value = LoanMoney;
            //paremeter[2].Value = BiJiaoFuHao;
            //int count = DbHelperSQL.ExecuteSql("PROC_MemLoan_Count",paremeter);
            #endregion
            //一  连接数据源
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            //二  显示
            string sql = @"select MemLoanId from MemLoan inner join Users on MemLoan.UserrId=Users.UserId   where LoanType='信用标' and UserName like '%" + UserName + "%'";
            if (LoanMoney != 0 && BiJiaoFuHao != "请选择")
            {
                sql += "and LoanMoney " + BiJiaoFuHao + " " + LoanMoney + "";
            }
            SqlCommand cmd = new SqlCommand(sql, conn);//要执行的sql语句  选择数据源
            SqlDataReader sdr = cmd.ExecuteReader();//执行查询操作  返回一个读取器（多个值）
            ////读取器中的数据不能直接显示，必须通过Read方法来读
            //while (sdr.Read())//如果读取到数据 返回true 将读到的数据添加到控件
            //{
            //    comboBox1.Items.Add(sdr[0]);//将数据显示到控件中  sdr.GetString(0)  sdr["dname"]
            //}
            //sdr.Close(); //关闭读取器对象

            //三  关闭数据源
            int i = 0;
            while (sdr.Read())
            {
                i++;
            }
            conn.Close();
            return i;
        }
        /// <summary>
        /// 成功案例展示
        /// </summary>
        /// <returns></returns>
        public Pager<MemLoanModel> Get_MemLoan_Selet(string RepaymentModeId, string ExamineState, int PageIndex, int PageSize)
        {
            SqlParameter[] Parameter = { new SqlParameter("@RepaymentModeId", SqlDbType.VarChar),
                                         new SqlParameter("@ExamineState",SqlDbType.VarChar),
                                         new SqlParameter("@PageIndex",SqlDbType.Int),
                                         new SqlParameter("@PageSize",SqlDbType.Int)
                                       };
            Parameter[0].Value = RepaymentModeId;
            Parameter[1].Value = ExamineState;
            Parameter[2].Value = PageIndex;
            Parameter[3].Value = PageSize;
            List<MemLoanModel> li = new List<MemLoanModel>();
            int totalcount = 0;
            DataTable Read = DbHelperSQL.RunProcedure("PROC_MemLoan_Select", Parameter, PageSize, PageIndex, out totalcount);
            for (int i = 0; i < Read.Rows.Count; i++)
            {
                MemLoanModel m = new MemLoanModel();
                m.AlreadyRaiseLoan = decimal.Parse(Read.Rows[i]["AlreadyRaiseLoan"].ToString());
                m.Annualrate = float.Parse(Read.Rows[i]["Annualrate"].ToString());
                m.EndLoanTime = DateTime.Parse(Read.Rows[i]["EndLoanTime"].ToString());
                m.ExamineState = Read.Rows[i]["ExamineState"].ToString();
                m.LaunchLoanTime = DateTime.Parse(Read.Rows[i]["LaunchLoanTime"].ToString());
                m.LoanExplain = Read.Rows[i]["LoanExplain"].ToString();
                m.LoanMoney = decimal.Parse(Read.Rows[i]["LoanMoney"].ToString());
                m.LoanPurpose = Read.Rows[i]["LoanPurpose"].ToString();
                m.LoanRaise = int.Parse(Read.Rows[i]["LoanRaise"].ToString());
                m.LoanTerm = int.Parse(Read.Rows[i]["LoanTerm"].ToString());
                m.LoanType = Read.Rows[i]["LoanType"].ToString();
                m.MemLoanId = int.Parse(Read.Rows[i]["MemLoanId"].ToString());
                m.PayInterest = decimal.Parse(Read.Rows[i]["PayInterest"].ToString());
                m.RepaymentDay = DateTime.Parse(Read.Rows[i]["RepaymentDay"].ToString());
                m.RepaymentModeId = Read.Rows[i]["RepaymentModeId"].ToString();
                m.RepaySumMoney = decimal.Parse(Read.Rows[i]["RepaySumMoney"].ToString());
                m.Title = Read.Rows[i]["Title"].ToString();
                m.TotalTransactions = int.Parse(Read.Rows[i]["TotalTransactions"].ToString());
                m.UserrId = int.Parse(Read.Rows[i]["UserrId"].ToString());
                m.UserName = Read.Rows[i]["UserName"].ToString();
                li.Add(m);
            }
            Pager<MemLoanModel> show = new Pager<MemLoanModel>();
            show.Data = li;
            show.PageCount = (int)Math.Ceiling(Convert.ToDecimal(totalcount) / Convert.ToDecimal(PageSize));
            show.PageIndex = PageIndex;
            show.PageSize = PageSize;
            show.IndexCount = totalcount / PageSize;
            if (totalcount%PageSize>0)
            {
                show.IndexCount += 1;
            }
            return show;
        }
    
    

       /// <summary>
       /// 根据ID查询具体案例信息
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
        public MemLoanModel Get_MemLoanOne(string id)
        {
            SqlParameter[] Paramter = { new SqlParameter("@MemLoanId", SqlDbType.VarChar) };
            Paramter[0].Value = id;
            MemLoanModel m = new MemLoanModel();
            SqlDataReader dt = DbHelperSQL.RunProcedure("PROC_MemLoan_One", Paramter);
            while (dt.Read())
            {
                m.MemLoanId = int.Parse(dt["MemLoanId"].ToString());
                m.AlreadyRaiseLoan = decimal.Parse(dt["AlreadyRaiseLoan"].ToString());
                m.Annualrate = float.Parse(dt["Annualrate"].ToString());
                m.EndLoanTime = DateTime.Parse(dt["EndLoanTime"].ToString());
                m.ExamineState = dt["ExamineState"].ToString();
                m.ExamineState = dt["ExamineState"].ToString();
                m.LaunchLoanTime = DateTime.Parse(dt["LaunchLoanTime"].ToString());
                m.LoanExplain = dt["LoanExplain"].ToString();
                m.LoanMoney = decimal.Parse(dt["LoanMoney"].ToString());
                m.LoanPurpose = dt["LoanPurpose"].ToString();
                m.LoanRaise = int.Parse(dt["LoanRaise"].ToString());
                m.LoanTerm = int.Parse(dt["LoanTerm"].ToString());
                m.LoanType = dt["LoanType"].ToString();
                m.PayInterest = decimal.Parse(dt["PayInterest"].ToString());
                m.RepaymentDay = DateTime.Parse(dt["RepaymentDay"].ToString());
                m.RepaymentModeId = dt["RepaymentModeId"].ToString();
                m.RepaySumMoney = decimal.Parse(dt["RepaySumMoney"].ToString());
                m.Title = dt["Title"].ToString();
                m.TotalTransactions = int.Parse(dt["TotalTransactions"].ToString());
                m.UserrId = int.Parse(dt["UserrId"].ToString());
            }
            return m;
        }
        /// <summary>
        /// 发布借款信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int MemLoanAdd(MemLoanModel model)
        {
            SqlParameter[] Parameter = { 
                                       new SqlParameter("@LoanMoney",SqlDbType.Decimal),
                                        new SqlParameter("@LoanPurpose",SqlDbType.VarChar),
                                         new SqlParameter("@LoanRaise",SqlDbType.Int),
                                          new SqlParameter("@Annualrate",SqlDbType.Float),
                                           new SqlParameter("@LoanTerm",SqlDbType.Int),

                                             new SqlParameter("@RepaymentModeId",SqlDbType.VarChar),
                                              new SqlParameter("@Title",SqlDbType.VarChar),
                                               new SqlParameter("@LoanExplain",SqlDbType.VarChar),
                                                new SqlParameter("@UserrId",SqlDbType.Int),
                                                 new SqlParameter("@LaunchLoanTime",SqlDbType.DateTime),

                                                 new SqlParameter("@EndLoanTime",SqlDbType.DateTime),
                                                new SqlParameter("@RepaymentDay",SqlDbType.DateTime),
                                               new SqlParameter("@PayInterest",SqlDbType.Decimal),
                                              new SqlParameter("@RepaySumMoney",SqlDbType.Decimal),
                                             new SqlParameter("@ExamineState",SqlDbType.Int),
                                            new SqlParameter("@AlreadyRaiseLoan",SqlDbType.Decimal),
                                           new SqlParameter("@TotalTransactions",SqlDbType.Int),
                                          new SqlParameter("@LoanType",SqlDbType.VarChar),
                                          new SqlParameter("@newID",SqlDbType.Int)
                                       };
            Parameter[0].Value = model.LoanMoney;
            Parameter[1].Value = model.LoanPurpose;
            Parameter[2].Value = model.LoanRaise;
            Parameter[3].Value = model.Annualrate;
            Parameter[4].Value = model.LoanTerm;

            Parameter[5].Value = model.RepaymentModeId;
            Parameter[6].Value = model.Title;
            Parameter[7].Value = model.LoanExplain;
            Parameter[8].Value = model.UserrId;
            Parameter[9].Value = model.LaunchLoanTime;

            Parameter[10].Value = model.EndLoanTime;
            Parameter[11].Value = model.RepaymentDay;
            Parameter[12].Value = model.PayInterest;
            Parameter[13].Value = model.RepaySumMoney;
            Parameter[14].Value = model.ExamineState;
            Parameter[15].Value = model.AlreadyRaiseLoan;
            Parameter[16].Value = model.TotalTransactions;
            Parameter[17].Value = model.LoanType;
            Parameter[18].Direction = ParameterDirection.ReturnValue;
            DbHelperSQL.RunProcedureReturnBool("Proc_MemLoanAdd", Parameter);
            int NewID = (int)Parameter[18].Value;

            return NewID;
        }

        /// <summary>
        /// 查询支付密码
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserrsModel SelectPayPwd(int id)
        {
            SqlParameter[] Parameter = { 
                                       new SqlParameter("@UserId",SqlDbType.Int)
                                       };
            Parameter[0].Value = id;
            UserrsModel model = new UserrsModel();
            SqlDataReader reader = DbHelperSQL.RunProcedure("Proc_SelectPayPwd", Parameter);
            reader.Read();
           
            model.PaymentPwd =Convert.ToInt32(reader["PaymentPwd"]);
            return model;
        }
        /// <summary>
        /// 修改支付密码
        /// </summary>
        /// <param name="id"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool UpdatePwd(int id, string pwd)
        {
            SqlParameter[] Parameter = { 
                                       new SqlParameter("@UserId",SqlDbType.Int),
                                       new SqlParameter("@PaymentPwd",SqlDbType.VarChar)
                                       };
            Parameter[0].Value = id;
            Parameter[1].Value = pwd;
            return DbHelperSQL.RunProcedureReturnBool("Proc_UpdatePayPwd", Parameter);
        }

        /// <summary>
        /// 显示所有的借款信息
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="Title"></param>
        /// <param name="MinTime"></param>
        /// <param name="MaxTime"></param>
        /// <param name="State"></param>
        /// <param name="Userid"></param>
        /// <returns></returns>
        public Pager<MemLoanModel> SelectMomLoanMone(int pageSize, int pageIndex, string Title, string MinTime, string MaxTime, string State, int Userid)
        {
            SqlParameter[] Parameter = { 
                                         new SqlParameter("@PageSize",SqlDbType.Int),
                                         new SqlParameter("@PageIndex",SqlDbType.Int),
                                         new SqlParameter("@Title",SqlDbType.VarChar),
                                         new SqlParameter("@TimeMin",SqlDbType.VarChar),
                                         new SqlParameter("@TimeMax",SqlDbType.VarChar),
                                         new SqlParameter("@State",SqlDbType.VarChar),
                                         new SqlParameter("@Usid",SqlDbType.Int),
                                       };
            Parameter[0].Value = pageSize;
            Parameter[1].Value = pageIndex;
            Parameter[2].Value = Title;
            Parameter[3].Value = MinTime;
            Parameter[4].Value = MaxTime;
            Parameter[5].Value = State;
            Parameter[6].Value = Userid;
            List<MemLoanModel> list = new List<MemLoanModel>();
            int PageSizeCount = 0;
            DataTable reader = DbHelperSQL.RunProcedure("Proc_SelectMomLoanMone", Parameter, pageSize, pageIndex, out PageSizeCount);
            for (int i = 0; i < reader.Rows.Count; i++)
            {
                MemLoanModel model = new MemLoanModel();
                model.MemLoanId = Convert.ToInt32(reader.Rows[i]["MemLoanId"]);
                model.Title = reader.Rows[i]["Title"].ToString();
                model.LoanMoney = decimal.Parse(reader.Rows[i]["LoanMoney"].ToString());
                model.LoanTerm = Convert.ToInt32(reader.Rows[i]["LoanTerm"]);
                model.RepaymentModeId = reader.Rows[i]["RepaymentModeId"].ToString();
                model.UserName = reader.Rows[i]["UserName"].ToString();
                model.UserrId = Convert.ToInt32(reader.Rows[i]["UserId"]);
                model.state = reader.Rows[i]["state"].ToString();
                model.LaunchLoanTime = Convert.ToDateTime(reader.Rows[i]["LaunchLoanTime"]);
                list.Add(model);
            }
            Pager<MemLoanModel> show = new Pager<MemLoanModel>();
            show.Data = list;
            show.PageCount = PageSizeCount;//(int)Math.Ceiling(Convert.ToDecimal(PageSizeCount) / Convert.ToDecimal(pageSize));
            show.PageSize = pageSize;
            show.PageIndex = pageIndex;
            show.IndexCount = PageSizeCount / pageSize;
            if (PageSizeCount%pageSize>0)
            {
                show.IndexCount += 1;
            }
            return show;
        }
        /// <summary>
        /// 修改审核状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public bool UpdateMemloanState(int id,int state)
        {
            SqlParameter[] Parameter ={
                                   new SqlParameter("@MemLoanId",SqlDbType.Int),
                                   new SqlParameter("@ExamineState",SqlDbType.Int)
                                   };
            Parameter[0].Value = id;
            Parameter[1].Value = state;
            return DbHelperSQL.RunProcedureReturnBool("Proc_UpdateMemloanState", Parameter);
        }
        #region 查询待还记录
        /// <summary>
        /// 查询待还记录
        /// </summary>
        /// <param name="id">借款编号</param>
        /// <returns></returns>
        public List<RepayDetailedModel> GetOne_Rep(int id)
        {
            #region 查询待还记录
            SqlParameter[] Parameter = { new SqlParameter("@MemLoanId", SqlDbType.Int) };
            Parameter[0].Value = id;
            SqlDataReader red = DbHelperSQL.RunProcedure("PROC_RepayDetailed_One", Parameter);
            List<RepayDetailedModel> li = new List<RepayDetailedModel>();
            while (red.Read())
            {
                RepayDetailedModel m = new RepayDetailedModel();
                m.MemInvestId = int.Parse(red["MemInvestId"].ToString());
                m.OverdueForfeit = decimal.Parse(red["OverdueForfeit"].ToString());
                m.P_RepaymentTime = red["P_RepaymentTime"].ToString();
                m.PaymentState = red["PaymentState"].ToString();
                m.R_RepaymentTime = red["R_RepaymentTime"].ToString();
                m.RepayDetailedId = int.Parse(red["RepayDetailedId"].ToString());
                
                m.RepaymentMoney = decimal.Parse(red["RepaymentMoney"].ToString());
                m.RushFee = decimal.Parse(red["RushFee"].ToString());
                m.SumDay = red["SumDay"].ToString();
                li.Add(m);
            }
            return li;
            #endregion
        }
        #endregion
    }
}
